# -*- coding: utf8 -*-

from project.mysql.table import Table

class TableImageVector10( Table ):

    _table_name = "image_vector10"

    def create( self ):
        cur = self.getCursor()

        # Create table
        cur.execute(
            "CREATE TABLE IF NOT EXISTS %s (" % self._table_name +
            "id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, "+
            "file_id INTEGER UNSIGNED NOT NULL, "+
            "image_id INTEGER UNSIGNED NOT NULL, "+
            "v0 TINYINT(3) UNSIGNED NOT NULL, "+
            "v1 TINYINT(3) UNSIGNED NOT NULL, "+
            "v2 TINYINT(3) UNSIGNED NOT NULL, "+
            "v3 TINYINT(3) UNSIGNED NOT NULL, "+
            "v4 TINYINT(3) UNSIGNED NOT NULL, "+
            "v5 TINYINT(3) UNSIGNED NOT NULL, "+
            "v6 TINYINT(3) UNSIGNED NOT NULL, "+
            "v7 TINYINT(3) UNSIGNED NOT NULL, "+
            "v8 TINYINT(3) UNSIGNED NOT NULL, "+
            "v9 TINYINT(3) UNSIGNED NOT NULL ) ENGINE %s" % self.getEngine()
        )
        try:
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX  `iv10_image` ( `image_id` )" % self._table_name
            )
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX  `iv10_file` ( `file_id` )" % self._table_name
            )
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX  `iv10` (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)" % self._table_name
            )
        except:
            pass
        cur.close()
        return


    def insert( self, file_id, image_id,
                          v0, v1, v2, v3, v4, v5, v6, v7, v8, v9 ):
        cur = self.getCursor()

        v_id = cur.execute(
            "INSERT INTO %s(" % self._table_name+
            "file_id, image_id, v0, v1, v2, v3, v4, v5, v6, v7, v8, v9"+
            ") VALUES ("+
            "%(file_id)s, %(image_id)s, "+
            "%(v0)s, %(v1)s, %(v2)s, %(v3)s, %(v4)s, %(v5)s, %(v6)s, %(v7)s, %(v8)s, %(v9)s"+
            ")", {
                "image_id":image_id,
                "file_id" : file_id,
                "v0":v0, "v1":v1, "v2":v2, "v3":v3, "v4":v4, "v5":v5, "v6":v6, "v7":v7, "v8":v8, "v9":v9
            }
        )
        self.getConnection().commit()
        cur.close()
        return v_id


    def get(self, image_id):
        cur = self.getCursor()
        cur.execute("SELECT * FROM %s " % self._table_name+
                    "WHERE image_id=%(value)s",
                {"value": image_id}
        )
        items = cur.fetchall()
        cur.close()
        return items


    def get_equal(self, image_vector):
        cur = self.getCursor()

        fetched = []

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9 IS NOT NULL"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8 IS NOT NULL AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7 IS NOT NULL AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6 IS NOT NULL AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5 IS NOT NULL AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4 IS NOT NULL AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3 IS NOT NULL AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1=%(v1)s AND " +
            "v2 IS NOT NULL AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0=%(v0)s AND " +
            "v1 IS NOT NULL AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0 IS NOT NULL AND " +
            "v1=%(v1)s AND " +
            "v2=%(v2)s AND " +
            "v3=%(v3)s AND " +
            "v4=%(v4)s AND " +
            "v5=%(v5)s AND " +
            "v6=%(v6)s AND " +
            "v7=%(v7)s AND " +
            "v8=%(v8)s AND " +
            "v9=%(v9)s"
            , image_vector
        )
        fetched.extend( cur.fetchall() )

        cur.close()

        items = {}
        for item in fetched:

            if item["image_id"] == image_vector["image_id"]:
                continue

            imid = str(item["image_id"])
            items[ imid ] = item

        return tuple(items.values())

    def get_similar(self, image_vector):
        cur = self.getCursor()
        proc = 0.05

        compare = {
            "id": image_vector["image_id"],
            "dv01": int(image_vector["v0"]*(1-proc)),
            "dv02": int(image_vector["v0"]*(1+proc)),
            "dv11": int(image_vector["v1"]*(1-proc)),
            "dv12": int(image_vector["v1"]*(1+proc)),
            "dv21": int(image_vector["v2"]*(1-proc)),
            "dv22": int(image_vector["v2"]*(1+proc)),
            "dv31": int(image_vector["v3"]*(1-proc)),
            "dv32": int(image_vector["v3"]*(1+proc)),
            "dv41": int(image_vector["v4"]*(1-proc)),
            "dv42": int(image_vector["v4"]*(1+proc)),
            "dv51": int(image_vector["v5"]*(1-proc)),
            "dv52": int(image_vector["v5"]*(1+proc)),
            "dv61": int(image_vector["v6"]*(1-proc)),
            "dv62": int(image_vector["v6"]*(1+proc)),
            "dv71": int(image_vector["v7"]*(1-proc)),
            "dv72": int(image_vector["v7"]*(1+proc)),
            "dv81": int(image_vector["v8"]*(1-proc)),
            "dv82": int(image_vector["v8"]*(1+proc)),
            "dv91": int(image_vector["v9"]*(1-proc)),
            "dv92": int(image_vector["v9"]*(1+proc)),
            }

        fetched = []

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9 IN NOT NULL"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8 IS NOT NULL AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7 IS NOT NULL AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6 IS NOT NULL AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5 IS NOT NULL AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4 IS NOT NULL AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3 IS NOT NULL AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2 IS NOT NULL AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0>=%(dv01)s AND v0<=%(dv02)s AND " +
            "v1 IS NOT NULL AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM `%s` WHERE " % self._table_name +

            "v0 IS NOT NULL AND " +
            "v1>=%(dv11)s AND v1<=%(dv12)s AND " +
            "v2>=%(dv21)s AND v2<=%(dv22)s AND " +
            "v3>=%(dv31)s AND v3<=%(dv32)s AND " +
            "v4>=%(dv41)s AND v4<=%(dv42)s AND " +
            "v5>=%(dv51)s AND v5<=%(dv52)s AND " +
            "v6>=%(dv61)s AND v6<=%(dv62)s AND " +
            "v7>=%(dv71)s AND v7<=%(dv72)s AND " +
            "v8>=%(dv81)s AND v8<=%(dv82)s AND " +
            "v9>=%(dv91)s AND v9<=%(dv92)s"
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.close()

        items = {}
        for item in fetched:

            if item["image_id"] == image_vector["image_id"]:
                continue

            imid = str(item["image_id"])
            items[ imid ] = item

        return tuple(items.values())

    def remove_file_images(self, file_id):
        cur = self.getCursor()
        rc = cur.execute("DELETE FROM `%s` " % self._table_name +
                         "WHERE file_id=%(id)s", {"id":file_id})
        self.getConnection().commit()
        cur.close()
        return rc

    pass
